package com.audi.box.adapter;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteAsusHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "audi.db";
    private static final int DATABASE_VERSION = 1;
    
    // TABLE DEVICE
    public static final String TABLE_DEVICE = "device";
    public static final String KEY_DEVICE_ID = "_device_id";
    public static final String KEY_DEVICE_NAME = "_device_name";
    public static final String KEY_DEVICE_ICON = "_device_icon";
    public static final String KEY_DEVICE_STATUS = "_device_status";
    public static final String KEY_DEVICE_TYPE = "_device_type";
    public static final String KEY_DEVICE_CONTROL_VALUE = "_device_control_value";
    public static final String KEY_DEVICE_CODE = "_device_code";
    public static final String KEY_DEVICE_BOX_ID = "_device_box_id";
    public static final String KEY_DEVICE_PLACE_ID = "_device_place_id";
    public static final String KEY_DEVICE_USER_ID = "_device_user_id";
    public static final String KEY_DEVICE_IS_DELETED = "_device_is_deleted";
    public static final String KEY_DEVICE_FAVORITE = "_device_favorite";
    public static final String CREATE_TABLE_DEVICE = "CREATE TABLE " + TABLE_DEVICE + "("
            
            + KEY_DEVICE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_DEVICE_NAME + " TEXT,"
            + KEY_DEVICE_ICON + " TEXT," + KEY_DEVICE_STATUS + " INTEGER," + KEY_DEVICE_TYPE
            + " INTEGER," + KEY_DEVICE_CONTROL_VALUE + " INTEGER," + KEY_DEVICE_CODE + " TEXT,"
            + KEY_DEVICE_BOX_ID + " INTEGER," + KEY_DEVICE_PLACE_ID + " INTEGER,"
            + KEY_DEVICE_USER_ID + " INTEGER," + KEY_DEVICE_IS_DELETED + " INTEGER DEFAULT 0 ,"
            + KEY_DEVICE_FAVORITE + " INTEGER  DEFAULT 0" + ")";
    
    // TABLE PLACE
    public static final String TABLE_PLACE = "place";
    public static final String KEY_PLACE_ID = "_place_id";
    public static final String KEY_PLACE_NAME = "_place_name";
    public static final String KEY_PLACE_ICON = "_place_icon";
    public static final String KEY_PLACE_CAPABILITY_TYPE = "_place_capability_type";
    public static final String KEY_PLACE_IS_DELETED = "_place_is_deleted";
    public static final String CREATE_TABLE_PLACE = "CREATE TABLE " + TABLE_PLACE + "("
            + KEY_PLACE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_PLACE_NAME + " TEXT,"
            + KEY_PLACE_ICON + " TEXT," + KEY_PLACE_CAPABILITY_TYPE + " INTEGER,"
            + KEY_PLACE_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE SCENE
    public static final String TABLE_SCENE = "scene";
    public static final String KEY_SCENE_ID = "_scene_id";
    public static final String KEY_SCENE_NAME = "_scene_name";
    public static final String KEY_SCENE_ICON = "_scene_icon";
    public static final String KEY_SCENE_COLOR = "_scene_color";
    public static final String KEY_SCENE_PLACE_ID = "_scene_place_id";
    public static final String KEY_SCENE_IS_DELETED = "_scene_is_deleted";
    public static final String CREATE_TABLE_SCENE = "CREATE TABLE " + TABLE_SCENE + "("
            + KEY_SCENE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_SCENE_NAME + " TEXT,"
            + KEY_SCENE_ICON + " TEXT," + KEY_SCENE_COLOR + " INTEGER," + KEY_SCENE_PLACE_ID
            + " INTEGER," + KEY_SCENE_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE SCENE_DEVICE
    public static final String TABLE_SCENE_DEVICE = "scene_device";
    public static final String KEY_SCENE_DEVICE_SCENE_ID = "_scene_device_scene_id";
    public static final String KEY_SCENE_DEVICE_DEVICE_ID = "_scene_device_device_id";
    public static final String KEY_SCENE_DEVICE_DEVICE_STATUS = "_scene_device_device_status";
    public static final String KEY_SCENE_DEVICE_IS_DELETED = "_scene_device_is_deleted";
    public static final String CREATE_TABLE_SCENE_DEVICE = "CREATE TABLE " + TABLE_SCENE_DEVICE
            + "(" + KEY_SCENE_DEVICE_SCENE_ID + " INTEGER," + KEY_SCENE_DEVICE_DEVICE_ID
            + " INTEGER," + KEY_SCENE_DEVICE_DEVICE_STATUS + " INTEGER,"
            + KEY_SCENE_DEVICE_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE ST_WHEN
    public static final String TABLE_ST_WHEN = "st_when";
    public static final String KEY_ST_WHEN_ID = "_st_when_id";
    public static final String KEY_ST_WHEN_DEVICE_ID = "_st_when_device_id";
    public static final String KEY_ST_WHEN_VALUE = "_st_when_value";
    public static final String KEY_ST_WHEN_TYPE = "_st_when_type";
    public static final String KEY_ST_WHEN_IS_DELETED = "_st_when_is_deleted";
    public static final String CREATE_TABLE_ST_WHEN = "CREATE TABLE " + TABLE_ST_WHEN + "("
            + KEY_ST_WHEN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ST_WHEN_DEVICE_ID
            + " INTEGER," + KEY_ST_WHEN_VALUE + " TEXT," + KEY_ST_WHEN_TYPE + " INTEGER,"
            + KEY_ST_WHEN_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE ST_TRIGGER
    public static final String TABLE_ST_TRIGGER = "st_trigger";
    public static final String KEY_ST_TRIGGER_ID = "_st_trigger_id";
    public static final String KEY_ST_TRIGGER_DEVICE_ID = "_st_trigger_device_id";
    public static final String KEY_ST_TRIGGER_VALUE = "_st_trigger_value";
    public static final String KEY_ST_TRIGGER_ACTION = "_st_trigger_action";
    public static final String KEY_ST_TRIGGER_IS_DELETED = "_st_trigger_is_deleted";
    public static final String CREATE_TABLE_ST_TRIGGER = "CREATE TABLE " + TABLE_ST_TRIGGER + "("
            + KEY_ST_TRIGGER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ST_TRIGGER_DEVICE_ID
            + " INTEGER," + KEY_ST_TRIGGER_VALUE + " TEXT," + KEY_ST_TRIGGER_ACTION + " INTEGER,"
            + KEY_ST_TRIGGER_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE ST_WHEN_TRIGGER
    public static final String TABLE_ST_WHEN_TRIGGER = "st_when_trigger";
    public static final String KEY_ST_WHEN_TRIGGER_WHEN_ID = "_st_when_trigger_when_id";
    public static final String KEY_ST_WHEN_TRIGGER_TRIGGER_ID = "_st_when_trigger_trigger_id";
    public static final String KEY_ST_WHEN_TRIGGER_NAME = "_st_when_trigger_name";
    public static final String KEY_ST_WHEN_TRIGGER_IS_DELETED = "_st_when_trigger_is_deleted";
    public static final String KEY_ST_WHEN_TRIGGER_ONOFF = "st_when_trigger_onoff";
    public static final String KEY_ST_WHEN_TRIGGER_NOTI = "st_when_trigger_nofi";
    public static final String CREATE_TABLE_ST_WHEN_TRIGGER = "CREATE TABLE "
            + TABLE_ST_WHEN_TRIGGER + "(" + KEY_ST_WHEN_TRIGGER_WHEN_ID + " INTEGER,"
            + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + " INTEGER," + KEY_ST_WHEN_TRIGGER_NAME + " TEXT,"
            + KEY_ST_WHEN_TRIGGER_IS_DELETED + " INTEGER DEFAULT 0," + KEY_ST_WHEN_TRIGGER_ONOFF
            + " INTEGER DEFAULT 1," + KEY_ST_WHEN_TRIGGER_NOTI
            + " INTEGER DEFAULT 1" + ")";
    
    // TABLE WIFI
    public static final String TABLE_WIFI = "wifi";
    public static final String KEY_WIFI_ID = "_wifi_id";
    public static final String KEY_WIFI_NAME = "_wifi_name";
    public static final String KEY_WIFI_PASSWORD = "_wifi_password";
    public static final String KEY_WIFI_CODE = "_wifi_code";
    public static final String KEY_WIFI_IS_DELETED = "_wifi_is_deleted";
    public static final String CREATE_TABLE_WIFI = "CREATE TABLE " + TABLE_WIFI + "(" + KEY_WIFI_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_WIFI_NAME + " TEXT," + KEY_WIFI_PASSWORD
            + " TEXT," + KEY_WIFI_CODE + " TEXT," + KEY_WIFI_IS_DELETED + " INTEGER DEFAULT 0"
            + ")";
    
    // TABLE BOX
    public static final String TABLE_BOX = "box";
    public static final String KEY_BOX_ID = "_box_id";
    public static final String KEY_BOX_NAME = "_box_name";
    public static final String KEY_BOX_PASSWORD = "_box_password";
    public static final String KEY_BOX_CODE = "_box_code";
    public static final String KEY_BOX_WIFI_ID = "_box_wifi_id";
    public static final String KEY_BOX_IS_DELETED = "_box_is_deleted";
    public static final String CREATE_TABLE_BOX = "CREATE TABLE " + TABLE_BOX + "(" + KEY_BOX_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_BOX_NAME + " TEXT," + KEY_BOX_PASSWORD
            + " TEXT," + KEY_BOX_CODE + " TEXT," + KEY_BOX_WIFI_ID + " INTEGER,"
            + KEY_BOX_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE USER
    public static final String TABLE_USER = "user";
    public static final String KEY_USER_ID = "_user_id";
    public static final String KEY_USER_NAME = "_user_name";
    public static final String KEY_USER_EMAIL = "_user_email";
    public static final String KEY_USER_ICON = "_user_icon";
    public static final String KEY_USER_STATUS = "_user_status";
    public static final String KEY_USER_IS_DELETED = "_user_is_deleted";
    public static final String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_USER_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_USER_NAME + " TEXT," + KEY_USER_EMAIL
            + " TEXT," + KEY_USER_ICON + " TEXT," + KEY_USER_STATUS + " INTEGER,"
            + KEY_USER_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    public static final String TABLE_HISTORY = "history";
    public static final String KEY_HISTORY_ID = "_history_id";
    public static final String KEY_HISTORY_TARGET_ID = "_history_target_id";
    public static final String KEY_HISTORY_TARGET_NAME = "_history_target_name";
    public static final String KEY_HISTORY_TARGET_ICON = "_history_target_icon";
    public static final String KEY_HISTORY_TARGET_TYPE = "_history_target_type";
    public static final String KEY_HISTORY_TARGET_ACTION = "_history_target_action";
    public static final String KEY_HISTORY_TARGET_SOURCE = "_history_target_source";
    public static final String KEY_HISTORY_TARGET_DESTINATION = "_history_target_destination";
    public static final String KEY_HISTORY_DESCRIPTION = "_history_description";
    public static final String KEY_HISTORY_USER = "_history_user";
    public static final String KEY_HISTORY_DATETIME = "_history_datetime";
    public static final String CREATE_TABLE_HISTORY = "CREATE TABLE " + TABLE_HISTORY + "("
            + KEY_HISTORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_TARGET_ID + " INTEGER,"
            + KEY_HISTORY_TARGET_NAME + " TEXT,"
            + KEY_HISTORY_TARGET_ICON + " TEXT,"
            + KEY_HISTORY_TARGET_TYPE + " TEXT,"
            + KEY_HISTORY_TARGET_ACTION + " TEXT,"
            + KEY_HISTORY_TARGET_SOURCE + " TEXT,"
            + KEY_HISTORY_TARGET_DESTINATION + " TEXT,"
            + KEY_HISTORY_DESCRIPTION + " TEXT,"
            + KEY_HISTORY_USER + " INTEGER,"
            + KEY_HISTORY_DATETIME + " INTEGER"
            + ")";
    
    // TABLE HISTORY_BOX_WIFI
    public static final String TABLE_HISTORY_BOX_WIFI = "history_box_wifi";
    public static final String KEY_HISTORY_BOX_WIFI_ID = "_history_box_wifi_id";
    public static final String KEY_HISTORY_BOX_WIFI_BOX_ID = "_history_box_wifi_box_id";
    public static final String KEY_HISTORY_BOX_WIFI_WIFI_ID = "_history_box_wifi_wifi_id";
    public static final String KEY_HISTORY_BOX_WIFI_USER_ID = "_history_box_wifi_user_id";
    public static final String KEY_HISTORY_BOX_WIFI_TYPE = "_history_box_wifi_type";
    public static final String KEY_HISTORY_BOX_WIFI_STATUS = "_history_box_wifi_status";
    public static final String KEY_HISTORY_BOX_WIFI_DATETIME = "_history_box_wifi_datetime";
    public static final String CREATE_TABLE_HISTORY_BOX_WIFI = "CREATE TABLE "
            + TABLE_HISTORY_BOX_WIFI + "(" + KEY_HISTORY_BOX_WIFI_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_HISTORY_BOX_WIFI_BOX_ID + " INTEGER,"
            + KEY_HISTORY_BOX_WIFI_WIFI_ID + " INTEGER," + KEY_HISTORY_BOX_WIFI_USER_ID
            + " INTEGER," + KEY_HISTORY_BOX_WIFI_TYPE + " INTEGER," + KEY_HISTORY_BOX_WIFI_STATUS
            + " TEXT," + KEY_HISTORY_BOX_WIFI_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_USER_ACTION
    public static final String TABLE_HISTORY_USER_ACTION = "history_user_action";
    public static final String KEY_HISTORY_USER_ACTION_ID = "_history_user_action_id";
    public static final String KEY_HISTORY_USER_ACTION_USER_ID = "_history_user_action_user_id";
    public static final String KEY_HISTORY_USER_ACTION_TYPE = "_history_user_action_type";
    public static final String KEY_HISTORY_USER_ACTION_BY_USER_ID = "_history_user_action_by_user_id";
    public static final String KEY_HISTORY_USER_ACTION_BOX_ID = "_history_user_action_box_id";
    public static final String KEY_HISTORY_USER_ACTION_DATETIME = "_history_user_action_datetime";
    public static final String CREATE_TABLE_HISTORY_USER_ACTION = "CREATE TABLE "
            + TABLE_HISTORY_USER_ACTION + "(" + KEY_HISTORY_USER_ACTION_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_HISTORY_USER_ACTION_USER_ID + " INTEGER,"
            + KEY_HISTORY_USER_ACTION_TYPE + " INTEGER," + KEY_HISTORY_USER_ACTION_BY_USER_ID
            + " INTEGER," + KEY_HISTORY_USER_ACTION_BOX_ID + " INTEGER,"
            + KEY_HISTORY_USER_ACTION_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_USER_HOME
    public static final String TABLE_HISTORY_USER_HOME = "history_user_home";
    public static final String KEY_HISTORY_USER_HOME_ID = "_history_user_home_id";
    public static final String KEY_HISTORY_USER_HOME_USER_ID = "_history_user_home_user_id";
    public static final String KEY_HISTORY_USER_HOME_TYPE = "_history_user_home_type";
    public static final String KEY_HISTORY_USER_HOME_DATETIME = "_history_user_home_datetime";
    public static final String CREATE_TABLE_HISTORY_USER_HOME = "CREATE TABLE "
            + TABLE_HISTORY_USER_HOME + "(" + KEY_HISTORY_USER_HOME_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_HISTORY_USER_HOME_USER_ID + " INTEGER,"
            + KEY_HISTORY_USER_HOME_TYPE + " INTEGER," + KEY_HISTORY_USER_HOME_DATETIME + " TEXT"
            + ")";
    
    // TABLE HISTORY_DEVICE
    public static final String TABLE_HISTORY_DEVICE = "history_device";
    public static final String KEY_HISTORY_DEVICE_ID = "_history_device_id";
    public static final String KEY_HISTORY_DEVICE_TYPE = "_history_device_type";
    public static final String KEY_HISTORY_DEVICE_USER_ID = "_history_device_user_id";
    public static final String KEY_HISTORY_DEVICE_DEVICE_ID = "_history_device_device_id";
    public static final String KEY_HISTORY_DEVICE_SCENE_ID = "_history_device_scene_id";
    public static final String KEY_HISTORY_DEVICE_DATETIME = "_history_device_datetime";
    
    public static final String CREATE_TABLE_HISTORY_DEVICE = "CREATE TABLE " + TABLE_HISTORY_DEVICE
            + "(" + KEY_HISTORY_DEVICE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_DEVICE_USER_ID + " INTEGER," + KEY_HISTORY_DEVICE_DEVICE_ID + " INTEGER,"
            + KEY_HISTORY_DEVICE_SCENE_ID + " INTEGER," + KEY_HISTORY_DEVICE_TYPE + " INTEGER,"
            
            + KEY_HISTORY_DEVICE_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_PLACE
    public static final String TABLE_HISTORY_PLACE = "history_place";
    public static final String KEY_HISTORY_PLACE_ID = "_history_place_id";
    public static final String KEY_HISTORY_PLACE_PLACE_ID = "_history_place_place_id";
    public static final String KEY_HISTORY_PLACE_TYPE = "_history_place_type";
    public static final String KEY_HISTORY_PLACE_USER_ID = "_history_place_user_id";
    public static final String KEY_HISTORY_PLACE_DATETIME = "_history_place_datetime";
    public static final String CREATE_TABLE_HISTORY_PLACE = "CREATE TABLE " + TABLE_HISTORY_PLACE
            + "(" + KEY_HISTORY_PLACE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_PLACE_PLACE_ID + " INTEGER," + KEY_HISTORY_PLACE_TYPE + " INTEGER,"
            + KEY_HISTORY_PLACE_USER_ID + " INTEGER," + KEY_HISTORY_PLACE_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_SCENE
    public static final String TABLE_HISTORY_SCENE = "history_scene";
    public static final String KEY_HISTORY_SCENE_ID = "_history_scene_id";
    public static final String KEY_HISTORY_SCENE_SCENE_ID = "_history_scene_scene_id";
    public static final String KEY_HISTORY_SCENE_TYPE = "_history_scene_type";
    public static final String KEY_HISTORY_SCENE_USER_ID = "_history_scene_user_id";
    public static final String KEY_HISTORY_SCENE_DATETIME = "_history_scene_datetime";
    public static final String CREATE_TABLE_HISTORY_SCENE = "CREATE TABLE " + TABLE_HISTORY_SCENE
            + "(" + KEY_HISTORY_SCENE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_SCENE_SCENE_ID + " INTEGER," + KEY_HISTORY_SCENE_TYPE + " INTEGER,"
            + KEY_HISTORY_SCENE_USER_ID + " INTEGER," + KEY_HISTORY_SCENE_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_SMART_TRIGGER
    public static final String TABLE_HISTORY_SMART_TRIGGER = "history_smart_trigger";
    public static final String KEY_HISTORY_SMART_TRIGGER_ID = "_history_smart_trigger_id";
    public static final String KEY_HISTORY_SMART_TRIGGER_WHEN_TRIGGER_ID = "_history_smart_trigger_when_trigger_id";
    public static final String KEY_HISTORY_SMART_TRIGGER_TYPE = "_history_smart_trigger_type";
    public static final String KEY_HISTORY_SMART_TRIGGER_USER_ID = "_history_smart_trigger_user_id";
    public static final String KEY_HISTORY_SMART_TRIGGER_DATETIME = "_history_smart_trigger_datetime";
    public static final String CREATE_TABLE_HISTORY_SMART_TRIGGER = "CREATE TABLE "
            + TABLE_HISTORY_SMART_TRIGGER + "(" + KEY_HISTORY_SMART_TRIGGER_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_HISTORY_SMART_TRIGGER_WHEN_TRIGGER_ID
            + " INTEGER," + KEY_HISTORY_SMART_TRIGGER_TYPE + " INTEGER,"
            + KEY_HISTORY_SMART_TRIGGER_USER_ID + " INTEGER," + KEY_HISTORY_SMART_TRIGGER_DATETIME
            + " TEXT" + ")";
    
    // TABLE HISTORY_WARNING
    public static final String TABLE_HISTORY_WARNING = "history_warning";
    public static final String KEY_HISTORY_WARNING_ID = "_history_warning_id";
    public static final String KEY_HISTORY_WARNING_DEVICE_ID = "_history_warning_device_id";
    public static final String KEY_HISTORY_WARNING_DATETIME = "_history_warning_datetime";
    public static final String CREATE_TABLE_HISTORY_WARNING = "CREATE TABLE "
            + TABLE_HISTORY_WARNING + "(" + KEY_HISTORY_WARNING_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_HISTORY_WARNING_DEVICE_ID + " INTEGER,"
            + KEY_HISTORY_WARNING_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_MOVE_PLACE_OF_DEVICE
    public static final String TABLE_HISTORY_MOVE_PLACE_OF_DEVICE = "history_move_place_of_device";
    public static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID = "_history_move_place_of_device_id";
    public static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID = "_history_move_place_of_device_device_id";
    public static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID = "_history_move_place_of_device_place_id";
    public static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID = "_history_move_place_of_device_user_id";
    public static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME = "_history_move_place_of_device_datetime";
    public static final String CREATE_TABLE_HISTORY_MOVE_PLACE_OF_DEVICE = "CREATE TABLE "
            + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "(" + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID
            + " INTEGER," + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID + " INTEGER,"
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID + " INTEGER,"
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME + " TEXT" + ")";
    
    // TABLE s
    public static final String TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE = "history_name_capability_of_place";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID = "_history_name_capability_of_place_id";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID = "_history_name_capability_of_place_place_id";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID = "_history_name_capability_of_place_user_id";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM = "_history_name_capability_of_place_rename_from";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO = "_history_name_capability_of_place_rename_to";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM = "_history_name_capability_of_place_capability_from";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO = "_history_name_capability_of_place_capability_to";
    public static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME = "_history_name_capability_of_place_datetime";
    public static final String CREATE_TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE = "CREATE TABLE "
            + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "("
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM + " TEXT,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO + " TEXT,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME + " TEXT" + ")";
    
    public SQLiteAsusHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(CREATE_TABLE_DEVICE);
        database.execSQL(CREATE_TABLE_PLACE);
        database.execSQL(CREATE_TABLE_SCENE);
        database.execSQL(CREATE_TABLE_SCENE_DEVICE);
        database.execSQL(CREATE_TABLE_ST_WHEN);
        database.execSQL(CREATE_TABLE_ST_TRIGGER);
        database.execSQL(CREATE_TABLE_ST_WHEN_TRIGGER);
        database.execSQL(CREATE_TABLE_WIFI);
        database.execSQL(CREATE_TABLE_BOX);
        database.execSQL(CREATE_TABLE_USER);
        database.execSQL(CREATE_TABLE_HISTORY);
        database.execSQL(CREATE_TABLE_HISTORY_BOX_WIFI);
        database.execSQL(CREATE_TABLE_HISTORY_USER_ACTION);
        database.execSQL(CREATE_TABLE_HISTORY_USER_HOME);
        database.execSQL(CREATE_TABLE_HISTORY_DEVICE);
        database.execSQL(CREATE_TABLE_HISTORY_PLACE);
        database.execSQL(CREATE_TABLE_HISTORY_SCENE);
        database.execSQL(CREATE_TABLE_HISTORY_SMART_TRIGGER);
        database.execSQL(CREATE_TABLE_HISTORY_WARNING);
        database.execSQL(CREATE_TABLE_HISTORY_MOVE_PLACE_OF_DEVICE);
        database.execSQL(CREATE_TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE);
        
        // add default record for PLACE UNCATEGORIZED
        database.execSQL("INSERT INTO " + TABLE_PLACE + "(" + KEY_PLACE_ID + "," + KEY_PLACE_NAME
                + "," + KEY_PLACE_ICON + "," + KEY_PLACE_CAPABILITY_TYPE + ","
                + KEY_PLACE_IS_DELETED + ") values(1, \"Uncategorized\", \"\", 0, 0)");
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE if EXISTS " + TABLE_DEVICE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_PLACE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_SCENE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_SCENE_DEVICE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_ST_WHEN);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_ST_TRIGGER);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_ST_WHEN_TRIGGER);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_WIFI);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_BOX);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_USER);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_BOX_WIFI);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_USER_ACTION);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_USER_HOME);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_DEVICE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_PLACE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_SCENE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_SMART_TRIGGER);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_WARNING);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE);
        db.execSQL("DROP TABLE if EXISTS " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE);
        onCreate(db);
    }
    
    public int sqlInsert(String table, String nullColumnHack, ContentValues values) {
        Log.d("SQLite", "INSERT INTO " + table);
        SQLiteDatabase db = this.getWritableDatabase();
        if (db == null) {
            return -1;
        }
        
        int result = 0;
        try {
            result = (int) db.insert(table, null, values);
        } catch (Exception e) {
            Log.e("SQLite", "INSERT ERROR: " + e.getMessage());
            return result;
        }
        db.close();
        return result;
    }
    
    public int
            sqlUpdate(String table, ContentValues values, String whereClause, String[] whereArgs) {
        Log.d("SQLite", "UPDATE " + table);
        SQLiteDatabase db = this.getWritableDatabase();
        
        if (db == null) {
            return -1;
        }
        
        int result = 0;
        try {
            result = db.update(table, values, whereClause, whereArgs);
        } catch (Exception e) {
            Log.e("SQLite", "UPDATE ERROR: " + e.getMessage());
            return result;
        }
        
        db.close();
        return result;
    }
}
